﻿<%@ WebHandler Language="C#" Class="RbShipFinishInfoDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
/**
 * Description: 热表转工任务完成情况看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.3.1
 */

public class RbShipFinishInfoDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getTotalBar":
                        r = getTotalBar(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getNotCompleteData":
                        r = getNotCompleteData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryBar":
                        r = getFactoryBar(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询
    Result getTotalBar(JObject jObject)
    {
        string factoryid = jObject["factoryid"].ToString();
        JArray selectdate = JArray.Parse(jObject["needcompletedate"].ToString());
        //查询转工数
        string sql = @"
SELECT NVL(COUNT(DISTINCT cci.togetherflag),0) shipCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND pdr.parentid IS NULL
      AND cr.toacceptdate IS NOT NULL
      AND cr.tofactoryid = '{0}'
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd')
      AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')";

        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtResult = dal.GetDataTable(sql);

        dtResult.Columns.Add("completeCount", typeof(string));// 已完成转工数
        dtResult.Columns.Add("intimeCount", typeof(string));// 按时完成转工任务数
        dtResult.Columns.Add("completeRate", typeof(string));// 完成率
        dtResult.Columns.Add("intimeRate", typeof(string));// 按时完成率
        dtResult.Columns.Add("delayDaysSum", typeof(string));// 超期天数

        //查询已完成转工任务数
        sql = @"
SELECT NVL(COUNT(DISTINCT cci.togetherflag),0) completeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtCompleteCount = dal.GetDataTable(sql);

        //查询按时完成转工任务数
        sql = @"
SELECT NVL(COUNT(DISTINCT cci.togetherflag),0) intimeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
      AND csr.completedate <= to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss')
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtInTimeCount = dal.GetDataTable(sql);

        //查询超期天数
        sql = @"
SELECT temp.togetherflag,CASE WHEN MAX(temp.delayDays) < 0 THEN 0 ELSE MAX(temp.delayDays) END delayDays
FROM (
     SELECT cci.containername,cci.togetherflag, 
            CASE WHEN pdr.parentid IS NOT NULL THEN 0
                 WHEN csr.CompleteDate IS NULL THEN TRUNC((SYSDATE - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2)
                 ELSE TRUNC((csr.CompleteDate - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2)
            END delayDays
     FROM Containershiprecord cr
     LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
     LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
     WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
           AND cr.tofactoryid = '{0}' AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
           AND pdr.parentid IS NULL 
           AND cr.toacceptdate IS NOT NULL
) Temp 
GROUP BY temp.togetherflag";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtDelayDays = dal.GetDataTable(sql);

        dtResult.Rows[0]["completeCount"] = dtCompleteCount.Rows[0]["completeCount"];
        dtResult.Rows[0]["intimeCount"] = dtInTimeCount.Rows[0]["intimeCount"];
        dtResult.Rows[0]["delayDaysSum"] = dtDelayDays.Compute("Sum(delayDays)", "");

        if (dtResult.Rows[0]["shipCount"].ToString() == "0")
        {
            dtResult.Rows[0]["completeRate"] = 0;
            dtResult.Rows[0]["intimeRate"] = 0;
        }
        else
        {
            int completeCount = Convert.ToInt32(dtResult.Rows[0]["completeCount"].ToString());
            int intimeCount = Convert.ToInt32(dtResult.Rows[0]["intimeCount"].ToString());
            double shipCount = Convert.ToDouble(dtResult.Rows[0]["shipCount"].ToString());

            double completeRate = (completeCount / shipCount) * 100;
            double intimeRate = (intimeCount / shipCount) * 100;
            dtResult.Rows[0]["completeRate"] = completeRate.ToString("0.00");
            dtResult.Rows[0]["intimeRate"] = intimeRate.ToString("0.00");
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getNotCompleteData(JObject jObject)
    {
        string factoryid = jObject["factoryid"].ToString();
        JArray selectdate = JArray.Parse(jObject["needcompletedate"].ToString());

        string sql = @"SELECT f.factoryname FROM mes_factory_mapping f WHERE f.factorycode IN ('600','601','607','609')";
        DataTable dtFactory = dal.GetDataTable(sql);
        dtFactory.Columns.Add("taskCount", typeof(Int32));
        dtFactory.Columns.Add("notReceiveCount", typeof(Int32));

        //待完成
        sql = @"
SELECT t.factoryname,COUNT(t.factoryname) taskCount 
FROM (
  SELECT DISTINCT cci.familylistnames productfamilyname,cci.containerno,cci.productname,cci.productdescription,f.factoryname
  FROM containershiprecord cr
  LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
  LEFT JOIN containercurrentinfo cci ON cr.containerid = cci.containerid
  LEFT JOIN Factory f ON cr.factoryid = f.factoryid
  LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
  WHERE cr.tofactoryid = '{0}' AND cr.toacceptdate IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2
        AND NVL(csr.recivedate,csr.completedate) IS NULL AND cr.needcompletedate IS NOT NULL AND pdr.parentid IS NULL
) t
GROUP BY t.factoryname";
        sql = string.Format(sql, factoryid);
        DataTable dtTaskInfo = dal.GetDataTable(sql);

        //待接收
        sql = @"
SELECT t.factoryname,COUNT(t.factoryname) notReceiveCount 
FROM (
  SELECT DISTINCT cci.familylistnames productfamilyname,cci.containerno,cci.productname,cci.productdescription,f.factoryname
  FROM containershiprecord cr
  LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
  LEFT JOIN containercurrentinfo cci ON cr.containerid = cci.containerid
  LEFT JOIN Factory f ON cr.factoryid = f.factoryid
  LEFT JOIN workflowstep ws ON ws.workflowstepid = cr.workflowstepid 
  LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
  WHERE cr.tofactoryid = '{0}' AND cr.toacceptdate IS NULL AND cr.isuse = 0 AND cci.containerstatus <> 2 AND cci.isinstore = 0
        AND cr.needcompletedate IS NOT NULL AND NVL(csr.recivedate,csr.completedate) IS NULL AND pdr.parentid IS NULL
) t
GROUP BY t.factoryname";
        sql = string.Format(sql, factoryid);
        DataTable dtNotReceiveInfo = dal.GetDataTable(sql);

        foreach (DataRow dr in dtFactory.Rows)
        {
            string filter = string.Format("factoryname = '{0}'", dr["factoryname"]);
            DataRow[] drTaskInfo = dtTaskInfo.Select(filter);
            DataRow[] drNotReceiveInfo = dtNotReceiveInfo.Select(filter);
            if (drTaskInfo.Length > 0)
            {
                dr["taskCount"] = Convert.ToInt32(drTaskInfo[0]["taskCount"].ToString());
            }
            else
            {
                dr["taskCount"] = 0;
            }

            if (drNotReceiveInfo.Length > 0)
            {
                dr["notReceiveCount"] = Convert.ToInt32(drNotReceiveInfo[0]["notReceiveCount"].ToString());
            }
            else
            {
                dr["notReceiveCount"] = 0;
            }
        }

        foreach (DataColumn col in dtFactory.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r = new Result();
        r.data = dtFactory;
        r.total = Convert.ToInt32(dtFactory.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getFactoryBar(JObject jObject)
    {
        string factoryid = jObject["factoryid"].ToString();
        JArray selectdate = JArray.Parse(jObject["needcompletedate"].ToString());
        string sql = @"SELECT f.factoryname FROM mes_factory_mapping f WHERE f.factorycode IN ('600','601','607','609')";
        DataTable dtFactory = dal.GetDataTable(sql);
        dtFactory.Columns.Add("shipCount", typeof(string));          // 转工数
        dtFactory.Columns.Add("completeCount", typeof(string));      // 已完成转工数
        dtFactory.Columns.Add("intimeCount", typeof(string));        // 按时完成转工任务数
        dtFactory.Columns.Add("completeRate", typeof(string));       // 完成率
        dtFactory.Columns.Add("intimeRate", typeof(string));         // 按时完成率
        dtFactory.Columns.Add("delayDaysSum", typeof(string));       // 超期天数

        //各分厂转工数
        sql = @"
SELECT f.factoryname,NVL(COUNT(DISTINCT cci.togetherflag),0) shipCount
FROM containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN mes_factory_mapping f ON cr.factoryid = f.factoryid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL
      AND f.factorycode IN ('600','601','607','609')
GROUP BY f.factoryname";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtShipCount = dal.GetDataTable(sql);

        //各分厂已完成转工任务数
        sql = @"
SELECT f.factoryname,NVL(COUNT(DISTINCT cci.togetherflag),0) completeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN mes_factory_mapping f ON f.factoryid = cr.factoryid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd') 
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL
      AND f.factorycode IN ('600','601','607','609')
GROUP BY f.factoryname";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtCompleteCount = dal.GetDataTable(sql);

        //查询按时完成转工任务数
        sql = @"
SELECT f.factoryname,NVL(COUNT(DISTINCT cci.togetherflag),0) intimeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN mes_factory_mapping f ON f.factoryid = cr.factoryid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' 
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd') 
      AND csr.completedate <= to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss')
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL
      AND f.factorycode IN ('600','601','607','609')
GROUP BY f.factoryname";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtInTimeCount = dal.GetDataTable(sql);

        //查询各分厂超期天数
        sql = @"
SELECT temp.factoryname,temp.togetherflag,CASE WHEN MAX(temp.delayDays) < 0 THEN 0 ELSE MAX(temp.delayDays) END delayDays
FROM (
     SELECT f.factoryname,cci.containername,cci.togetherflag,
            CASE WHEN pdr.parentid IS NOT NULL THEN 0
                 WHEN csr.CompleteDate IS NULL THEN TRUNC((SYSDATE - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2)
                 ELSE TRUNC((csr.CompleteDate - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2)
            END delayDays
     FROM Containershiprecord cr
     LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
     LEFT JOIN mes_factory_mapping f ON f.factoryid = cr.factoryid
     LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
     WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
           AND cr.tofactoryid = '{0}' AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd') 
           AND pdr.parentid IS NULL 
           AND cr.toacceptdate IS NOT NULL
           AND f.factorycode IN ('600','601','607','609')
) Temp 
GROUP BY temp.togetherflag,temp.factoryname";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtDelayDays = dal.GetDataTable(sql);

        foreach (DataRow dr in dtFactory.Rows)
        {
            string filter = string.Format("factoryname = '{0}'", dr["factoryname"]);

            DataRow[] drShipCount = dtShipCount.Select(filter);
            DataRow[] drCompleteCount = dtCompleteCount.Select(filter);
            DataRow[] drIntimeCount = dtInTimeCount.Select(filter);
            DataRow[] drDelayDays = dtDelayDays.Select(filter);

            if (drShipCount.Length > 0)
            {
                dr["shipCount"] = drShipCount[0]["shipCount"];
            }
            else
            {
                dr["shipCount"] = "0";
            }

            if (drCompleteCount.Length > 0)
            {
                dr["completeCount"] = drCompleteCount[0]["completeCount"];
            }
            else
            {
                dr["completeCount"] = "0";
            }

            if (drIntimeCount.Length > 0)
            {
                dr["intimeCount"] = drIntimeCount[0]["intimeCount"];
            }
            else
            {
                dr["intimeCount"] = "0";
            }

            if (drDelayDays.Length > 0)
            {
                dr["delayDaysSum"] = dtDelayDays.Compute("Sum(delayDays)", filter);
            }
            else
            {
                dr["delayDaysSum"] = "0";
            }

            if (dr["shipCount"].ToString() == "0")
            {
                dr["completeRate"] = 0;
                dr["intimeRate"] = 0;
            }
            else
            {
                int completeCount = Convert.ToInt32(dr["completeCount"].ToString());
                int intimeCount = Convert.ToInt32(dr["intimeCount"].ToString());
                double shipCount = Convert.ToDouble(dr["shipCount"].ToString());

                double completeRate = (completeCount / shipCount) * 100;
                double intimeRate = (intimeCount / shipCount) * 100;
                dr["completeRate"] = completeRate.ToString("0.00");
                dr["intimeRate"] = intimeRate.ToString("0.00");
            }
        }

        foreach (DataColumn col in dtFactory.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r = new Result();
        r.data = dtFactory;
        r.total = Convert.ToInt32(dtFactory.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}